package com.xjj.service.sqlprovider;

import com.xjj.entity.Advice;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

/**
 * @Description advice sqlprovider
 * @Author zhangjin
 * @Date 2022/10/20 15:51
 */
public class AdviceProvider {
    public String update(Advice advice){
        return new SQL(){
            {
                UPDATE("xjj_advice");
                if (StringUtils.hasLength(advice.getTitle())) {
                    SET("title=#{title}");
                }
                if (advice.getKlassId()!=null || advice.getKlassId()!=0) {
                    SET("klass_id=#{klassId}");
                }
                if (StringUtils.hasLength(advice.getTime())) {
                    SET("time=#{time}");
                }
                if (StringUtils.hasLength(advice.getFileUrl())) {
                    SET("file_url=#{fileUrl}");
                }
                if (StringUtils.hasLength(advice.getContent())) {
                    SET("content=#{content}");
                }
                if (advice.getAdviceCategoryId()!=null || advice.getAdviceCategoryId()!=0) {
                    SET("advice_category_id=#{adviceCategoryId}");
                }
                WHERE("id=#{id}");
            }
        }.toString();
    }

    /**
     * 多条件查询条件：开始时间，结束时间，通知类别
     * @param advice
     * @return
     */
    public String select(Advice advice){
        return new SQL(){
            {
                // 多条件查询，左外连接班级表，全连接 管理员表 通知类别表
                SELECT("a.id,a.time,a.title,a.content,a.file_url fileUrl,a.pub_range pubRange,m.name publisher,c.name catogeryName,k.name classname");
                FROM("xjj_advice a");
                JOIN("xjj_manager m on a.publisher_id=m.id");
                LEFT_OUTER_JOIN("xjj_klass k on a.klass_id=k.id");
                JOIN("xjj_advice_category c on a.advice_category_id=c.id");
                if (advice!=null){
                    if (StringUtils.hasLength(advice.getStartTime())){
                        WHERE("time>= #{startTime}");
                    }
                    if (StringUtils.hasLength(advice.getEndTime())){
                        WHERE("time<= #{endTime}");
                    }
                    if (advice.getAdviceCategoryId()!=null && advice.getAdviceCategoryId()!=0){
                        WHERE("advice_category_id=#{adviceCategoryId}");
                    }
                    if (StringUtils.hasLength(advice.getPublisher())){
                        WHERE("m.name like CONCAT('%',#{publisher},'%')");
                    }
                    if (advice.getPublisherId()!=null){
                        WHERE("publisher_id =#{publisherId}");
                    }
                }
            }
        }.toString();
    }
}
